package vn.edu.ptithcm.pim.dao;

import java.util.List;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.LockMode;
import org.hibernate.Transaction;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import vn.edu.ptithcm.pim.constant.Functions;
import vn.edu.ptithcm.pim.constant.TaskStatuses;
import vn.edu.ptithcm.pim.dom.Project;
import vn.edu.ptithcm.pim.dom.Task;
import vn.edu.ptithcm.pim.query.ProjectQuery;

@SuppressWarnings({ "rawtypes" })
public class ProjectDao extends HibernateDaoSupport {

	public void save(Project project) {
		try {
			Transaction tx = getSession().beginTransaction();
			getHibernateTemplate().save(project);
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public void saveOrUpdate(Project project) {
		try {
			Transaction tx = getSession().beginTransaction();
			if (project.getProjectid() != null) {
				getHibernateTemplate().merge(project);
			} else {
				getHibernateTemplate().saveOrUpdate(project);
			}

			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public void lock(Project project) {
		try {
			Transaction tx = getSession().beginTransaction();
			getHibernateTemplate().lock(project, LockMode.NONE);
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public void delete(Project project) {
		try {
			Transaction tx = getSession().beginTransaction();
			getHibernateTemplate().delete(project);
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public void deleteById(java.lang.Long id) {
		try {
			Transaction tx = getSession().beginTransaction();
			StringBuilder sql = new StringBuilder();
			sql.append("delete Project as project where project.projectid ='")
					.append(id).append("'");
			getHibernateTemplate().bulkUpdate(sql.toString());
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public Project merge(Project project) {
		try {
			return (Project) getHibernateTemplate().merge(project);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public Project findById(java.lang.Long id) {
		try {
			return (Project) getHibernateTemplate().get(Project.class, id);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List findByExample(Project project) {
		try {
			return getHibernateTemplate().findByExample("Project", project);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getAll() {
		try {
			Criteria crit = getSession().createCriteria(Project.class);
			crit.addOrder(Order.asc("name"));
			return crit.list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public Project findByNumber(int number) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Project as project where project.number ='")
					.append(number).append("'");
			List result = getHibernateTemplate().find(sql.toString());
			return (result != null && result.size() == 1) ? (Project) result
					.get(0) : null;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List findByCustomer(java.lang.Long customerId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"from Project as project where project.customer.customerid ='")
					.append(customerId).append("' order by project.name");
			return getHibernateTemplate().find(sql.toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List findByStatus(java.lang.Long projectStatusId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"from Project as project where project.projectStatus.statusid ='")
					.append(projectStatusId).append("' order by project.name");
			return getHibernateTemplate().find(sql.toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	public List<Project> findByQuery(ProjectQuery query, int max, int offset) {
		try {
			String searchKey = StringUtils.trimToEmpty(query.getSearchKey());
			if ("Keyword...".equals(searchKey)) {
				searchKey = "";
			}
			Criteria c = getSession().createCriteria(Project.class);
			if (!searchKey.isEmpty()) {
				Criterion name = Restrictions.ilike("name", "%" + searchKey
						+ "%");
				c.createAlias("customer", "customer",
						CriteriaSpecification.INNER_JOIN);
				Criterion customerName = Restrictions.ilike("customer.name",
						"%" + searchKey + "%");
				c.createAlias("group", "group",
						CriteriaSpecification.INNER_JOIN);
				c.createAlias("group.employee", "employee",
						CriteriaSpecification.INNER_JOIN);
				Criterion visa = Restrictions.ilike("employee.visa", "%"
						+ searchKey + "%");
				Disjunction disjunction = Restrictions.disjunction();
				disjunction.add(name);
				disjunction.add(customerName);
				disjunction.add(visa);
				c.add(disjunction);
			}
			if (query.getStatusId().intValue() != 0) {
				c.createAlias("projectStatus", "status");
				c.add(Restrictions.eq("status.statusid", query.getStatusId()));
			}
			c.addOrder(Order.asc("startdate"));
			return c.setFirstResult(offset).setMaxResults(max).list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countByQuery(ProjectQuery query) {
		try {
			String searchKey = StringUtils.trimToEmpty(query.getSearchKey());
			if ("Keyword...".equals(searchKey)) {
				searchKey = "";
			}
			Criteria c = getSession().createCriteria(Project.class);
			if (!searchKey.isEmpty()) {
				Criterion name = Restrictions.ilike("name", "%" + searchKey
						+ "%");
				c.createAlias("customer", "customer",
						CriteriaSpecification.INNER_JOIN);
				Criterion customerName = Restrictions.ilike("customer.name",
						"%" + searchKey + "%");
				c.createAlias("group", "group",
						CriteriaSpecification.INNER_JOIN);
				c.createAlias("group.employee", "employee",
						CriteriaSpecification.INNER_JOIN);
				Criterion visa = Restrictions.ilike("employee.visa", "%"
						+ searchKey + "%");
				Disjunction disjunction = Restrictions.disjunction();
				disjunction.add(name);
				disjunction.add(customerName);
				disjunction.add(visa);
				c.add(disjunction);
			}
			if (query.getStatusId().intValue() != 0) {
				c.createAlias("projectStatus", "status");
				c.add(Restrictions.eq("status.statusid", query.getStatusId()));
			}
			return Integer.parseInt(c.setProjection(Projections.rowCount())
					.list().get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getTasksByPriority(java.lang.Long priorityId,
			java.lang.Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"from Task as task where task.taskPriority.priorityid ='")
					.append(priorityId)
					.append("' and task.project.projectid ='")
					.append(projectId)
					.append("' order by task.createdtime desc");
			return getHibernateTemplate().find(sql.toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countTasksByStatus(java.lang.Long taskStatusId,
			java.lang.Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"select count(*) from Task as task where task.taskStatus.statusid ='")
					.append(taskStatusId)
					.append("' and task.project.projectid ='")
					.append(projectId).append("'");
			return Integer.parseInt(getHibernateTemplate().find(sql.toString())
					.get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getTasksByStatus(java.lang.Long taskStatusId,
			java.lang.Long projectId, int max, int offset) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.taskStatus.statusid ='")
					.append(taskStatusId)
					.append("' and task.project.projectid ='")
					.append(projectId)
					.append("' order by task.createdtime desc");
			return getSession().createQuery(sql.toString())
					.setFirstResult(offset).setMaxResults(max).list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getTasksByCategory(java.lang.Long categoryId,
			java.lang.Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"from Task as task where task.taskCategory.categoryid ='")
					.append(categoryId)
					.append("' and task.project.projectid ='")
					.append(projectId)
					.append("' order by task.createdtime desc");
			return getHibernateTemplate().find(sql.toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countTasksByCategoryAndStatusType(java.lang.Long categoryId,
			String statusType, java.lang.Long projectId) {
		try {
			Criteria c = getSession().createCriteria(Task.class);
			c.createAlias("project", "project");
			c.add(Restrictions.eq("project.projectid", projectId));
			c.createAlias("taskCategory", "category");
			c.add(Restrictions.eq("category.categoryid", categoryId));
			c.createAlias("taskStatus", "status");
			if ("open".equals(statusType)) {
				c.add(Restrictions.not(Restrictions.eq("status.statusid",
						new Long(TaskStatuses.CLOSED))));
			} else if ("closed".equals(statusType)) {
				c.add(Restrictions.eq("status.statusid", new Long(
						TaskStatuses.CLOSED)));
			}
			c.setProjection(Projections.rowCount());
			return Integer.parseInt(c.list().get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countTasksByPriorityAndStatusType(java.lang.Long priorityId,
			String statusType, java.lang.Long projectId) {
		try {
			Criteria c = getSession().createCriteria(Task.class);
			c.createAlias("project", "project");
			c.add(Restrictions.eq("project.projectid", projectId));
			c.createAlias("taskPriority", "priority");
			c.add(Restrictions.eq("priority.priorityid", priorityId));
			c.createAlias("taskStatus", "status");
			if ("open".equals(statusType)) {
				c.add(Restrictions.not(Restrictions.eq("status.statusid",
						new Long(TaskStatuses.CLOSED))));
			} else if ("closed".equals(statusType)) {
				c.add(Restrictions.eq("status.statusid", new Long(
						TaskStatuses.CLOSED)));
			}
			c.setProjection(Projections.rowCount());
			return Integer.parseInt(c.list().get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getTasksBySeverity(java.lang.Long severityId,
			java.lang.Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"from Task as task where task.taskSeverity.severityid ='")
					.append(severityId)
					.append("' and task.project.projectid ='")
					.append(projectId)
					.append("' order by order by task.createdtime desc");
			return getHibernateTemplate().find(sql.toString());
		} catch (RuntimeException re) {

			throw re;
		}
	}

	public int countTasksBySeverityAndStatusType(java.lang.Long severityId,
			String statusType, java.lang.Long projectId) {
		try {
			Criteria c = getSession().createCriteria(Task.class);
			c.createAlias("project", "project");
			c.add(Restrictions.eq("project.projectid", projectId));
			c.createAlias("taskSeverity", "severity");
			c.add(Restrictions.eq("severity.severityid", severityId));
			c.createAlias("taskStatus", "status");
			if ("open".equals(statusType)) {
				c.add(Restrictions.not(Restrictions.eq("status.statusid",
						new Long(TaskStatuses.CLOSED))));
			} else if ("closed".equals(statusType)) {
				c.add(Restrictions.eq("status.statusid", new Long(
						TaskStatuses.CLOSED)));
			}
			c.setProjection(Projections.rowCount());
			return Integer.parseInt(c.list().get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countTasksByAssignedToAndStatusType(String assignedTo,
			String statusType, java.lang.Long projectId) {
		try {
			Criteria c = getSession().createCriteria(Task.class);
			c.createAlias("project", "project");
			c.add(Restrictions.eq("project.projectid", projectId));
			c.add(Restrictions.eq("assignedto", assignedTo));
			c.createAlias("taskStatus", "status");
			if ("open".equals(statusType)) {
				c.add(Restrictions.not(Restrictions.eq("status.statusid",
						new Long(TaskStatuses.CLOSED))));
			} else if ("closed".equals(statusType)) {
				c.add(Restrictions.eq("status.statusid", new Long(
						TaskStatuses.CLOSED)));
			}
			c.setProjection(Projections.rowCount());
			return Integer.parseInt(c.list().get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countTasksByReportedAndStatusType(String reporter,
			String statusType, java.lang.Long projectId) {
		try {
			Criteria c = getSession().createCriteria(Task.class);
			c.createAlias("project", "project");
			c.add(Restrictions.eq("project.projectid", projectId));
			c.add(Restrictions.eq("reporter", reporter));
			c.createAlias("taskStatus", "status");
			if ("open".equals(statusType)) {
				c.add(Restrictions.not(Restrictions.eq("status.statusid",
						new Long(TaskStatuses.CLOSED))));
			} else if ("closed".equals(statusType)) {
				c.add(Restrictions.eq("status.statusid", new Long(
						TaskStatuses.CLOSED)));
			}
			c.setProjection(Projections.rowCount());
			return Integer.parseInt(c.list().get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getTasksByReporter(String reporter, java.lang.Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.reporter like '%")
					.append(reporter)
					.append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' order by order by task.createdtime desc");
			return getHibernateTemplate().find(sql.toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getTasksByAssignedTo(String assignedto, java.lang.Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.assignedto like '%")
					.append(assignedto)
					.append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' order by task.createdtime desc");
			return getHibernateTemplate().find(sql.toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getTasksByAssignedTo(String assignedto,
			java.lang.Long statusId, java.lang.Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.assignedto like '%")
					.append(assignedto)
					.append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' and task.taskStatus.statusid ='")
					.append(statusId + "' order by task.createdtime desc");
			return getHibernateTemplate().find(sql.toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getAllMembers(Long projectId) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"select efip.employee from EmployeeFunctionInProject as efip where efip.project.projectid='")
				.append(projectId).append("' order by efip.employee.firstname");
		return getHibernateTemplate().find(sql.toString());
	}

	public List getAllMembers_(Long projectId) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"select efip from EmployeeFunctionInProject as efip where efip.project.projectid='")
				.append(projectId)
				.append("' and efip.function.functionid != '")
				.append(Functions.GL)
				.append("' order by efip.employee.firstname");
		return getHibernateTemplate().find(sql.toString());
	}

	public List getMembersByFunction(Long projectId, Long functionId) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"select efip.employee from EmployeeFunctionInProject as efip where efip.project.projectid='")
				.append(projectId).append("' and efip.function.functionid='")
				.append(functionId)
				.append("' order by efip.employee.firstname");
		return getHibernateTemplate().find(sql.toString());
	}

	public List getUnResolvedTasks(Long projectId, String assignedto, int max,
			int offset) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.assignedto like '%")
					.append(assignedto)
					.append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' and task.taskStatus.statusid not in(")
					.append(TaskStatuses.RESOLVED).append(",")
					.append(TaskStatuses.CLOSED)
					.append(") order by task.createdtime desc");
			return getSession().createQuery(sql.toString())
					.setFirstResult(offset).setMaxResults(max).list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countUnResolvedTasks(Long projectId, String assignedto) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"select count(distinct task) from Task as task where task.assignedto like '%")
					.append(assignedto)
					.append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' and task.taskStatus.statusid not in(")
					.append(TaskStatuses.RESOLVED).append(",")
					.append(TaskStatuses.CLOSED).append(")");
			return Integer.parseInt(getHibernateTemplate().find(sql.toString())
					.get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getReportedByMeTasks(Long projectId, String visa, int max,
			int offset) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.reporter like '%")
					.append(visa).append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' and task.taskStatus.statusid not in(")
					.append(TaskStatuses.RESOLVED).append(",")
					.append(TaskStatuses.CLOSED)
					.append(") order by task.createdtime desc");
			return getSession().createQuery(sql.toString())
					.setFirstResult(offset).setMaxResults(max).list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countReportedByMeTasks(Long projectId, String visa) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"select count(*) from Task as task where task.reporter like '%")
					.append(visa).append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' and task.taskStatus.statusid not in(")
					.append(TaskStatuses.RESOLVED).append(",")
					.append(TaskStatuses.CLOSED).append(")");
			return Integer.parseInt(getHibernateTemplate().find(sql.toString())
					.get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getMonitoredByMeTasks(Long projectId, String visa, int max,
			int offset) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.monitoredby like '%")
					.append(visa).append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' and task.taskStatus.statusid not in(")
					.append(TaskStatuses.RESOLVED).append(",")
					.append(TaskStatuses.CLOSED)
					.append(") order by task.createdtime desc");
			return getSession().createQuery(sql.toString())
					.setFirstResult(offset).setMaxResults(max).list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countMonitoredByMeTasks(Long projectId, String visa) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"select count(*) from Task as task where task.monitoredby like '%")
					.append(visa).append("%' and task.project.projectid ='")
					.append(projectId)
					.append("' and task.taskStatus.statusid not in(")
					.append(TaskStatuses.RESOLVED).append(",")
					.append(TaskStatuses.CLOSED).append(")");
			return Integer.parseInt(getHibernateTemplate().find(sql.toString())
					.get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getRecentlyModifiedTasks(Long projectId, int max, int offset) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.project.projectid ='")
					.append(projectId)
					.append("' and day(current_date()) - day(task.lastchanged) <= 7 ")
					.append("  and task.taskStatus.statusid !=")
					.append(TaskStatuses.CLOSED)
					.append(" order by task.lastchanged desc");
			return getSession().createQuery(sql.toString())
					.setFirstResult(offset).setMaxResults(max).list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countRecentlyModifiedTasks(Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"select count(*) from Task as task where task.project.projectid ='")
					.append(projectId)
					.append("' and day(current_date()) - day(task.lastchanged) <= 7 ")
					.append("  and task.taskStatus.statusid !=")
					.append(TaskStatuses.CLOSED);
			return Integer.parseInt(getHibernateTemplate().find(sql.toString())
					.get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getDelayTasks(Long projectId, int max, int offset) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("from Task as task where task.project.projectid ='")
					.append(projectId)
					.append("' and day(current_date()) - day(task.endtime) > 0 ")
					.append("  and task.taskStatus.statusid not in(")
					.append(TaskStatuses.RESOLVED).append(",")
					.append(TaskStatuses.CLOSED).append(")")
					.append(" order by task.endtime desc");
			return getSession().createQuery(sql.toString())
					.setFirstResult(offset).setMaxResults(max).list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countDelayTasks(Long projectId) {
		try {
			StringBuilder sql = new StringBuilder();
			sql.append(
					"select count(*) from Task as task where task.project.projectid ='")
					.append(projectId)
					.append("' and day(current_date()) - day(task.endtime) > 0 ")
					.append("  and task.taskStatus.statusid not in(")
					.append(TaskStatuses.RESOLVED).append(",")
					.append(TaskStatuses.CLOSED).append(")");
			return Integer.parseInt(getHibernateTemplate().find(sql.toString())
					.get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int[] getAllNumbers() {
		try {
			Criteria c = getSession().createCriteria(Project.class);
			c.setProjection(Projections.property("number"));
			List results = c.list();

			int[] numbers = new int[results.size()];
			for (Object object : results) {
				ArrayUtils.add(numbers, Integer.parseInt(object.toString()));
			}

			return numbers;
		} catch (RuntimeException re) {
			throw re;
		}
	}
}
